﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

using SubjectWorkShop.Core.IDAL;
using SubjectWorkShop.Core.Model;
using SubjectWorkShop.Core.DBUtility;

namespace SubjectWorkShop.Core.SQLServerDAL
{
    public class Subject : ISubject
    {
        #region "SQL语句"
        private const string SQL_SELECT_SUBJECTS = "SELECT top 5 id, name, keywords, url, image, des, time_add, weight, state  FROM t_subject";     //..e要用分页
        private const string SQL_SELECT_SUBJECT = "SELECT id, name, keywords, url, image, des, time_add, weight, state  FROM t_subject WHERE id = @id";
        private const string PARM_SUBJECT_ID = "@id";

        #endregion

        #region ISubject实现

        /// <summary>
        /// 根据给定ID获取专题信息
        /// </summary>		
        /// <returns>返回专题的 SubjectInfo 类, 若无相信专题则返回 null</returns>
        public SubjectInfo getSubjectById(int id) 
        {
            SubjectInfo subject = null;
            SqlParameter parm = new SqlParameter(PARM_SUBJECT_ID, SqlDbType.Int);
            parm.Value = id;

            //执行查询
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_SUBJECT, parm))
            {
                if (rdr.Read())
                    subject = new SubjectInfo(rdr.GetSqlInt32(0), rdr.GetSqlString(1), rdr.GetSqlString(2), rdr.GetSqlString(4), rdr.GetSqlString(4), rdr.GetSqlString(5), rdr.GetSqlDateTime(6), rdr.GetSqlInt16(7), rdr.GetSqlInt16(8));
            }
            return subject;
        }

        /// <summary>
        /// 获取所有专题信息
        /// </summary>		
        /// <returns>返回专题的 SubjectInfo 类</returns>
        public IList<SubjectInfo> getSubjects()
        {
            IList<SubjectInfo> subjects = new List<SubjectInfo>();

            //Execute a query to read the subjects
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_SUBJECTS, null))
            {
                while (rdr.Read())
                {
                    //SubjectInfo sub = new SubjectInfo(rdr.GetSqlInt32(0), rdr.GetSqlString(1), rdr.GetSqlString(2), rdr.GetSqlString(3), rdr.GetSqlString(4), rdr.GetSqlDateTime(5), rdr.GetSqlInt16(6), rdr.GetSqlInt16(7));
                    //SELECT id, name, keywords, image, des, time_add, weight, state  FROM t_subject
                    SubjectInfo sub = new SubjectInfo();
                    try
                    {
                        if (!rdr.IsDBNull(0)) sub.ID = rdr.GetSqlInt32(0);
                        if (!rdr.IsDBNull(1)) sub.Name = rdr.GetSqlString(1);
                        if (!rdr.IsDBNull(2)) sub.Keywords = rdr.GetSqlString(2);
                        if (!rdr.IsDBNull(3)) sub.Url = rdr.GetSqlString(3);
                        if (!rdr.IsDBNull(4)) sub.Image = rdr.GetSqlString(4);
                        if (!rdr.IsDBNull(5)) sub.Des = rdr.GetSqlString(5);
                        if (!rdr.IsDBNull(6)) sub.TimeAdd = rdr.GetSqlDateTime(6);
                        if (!rdr.IsDBNull(7)) sub.Weight = rdr.GetSqlByte(7);        
                        if (!rdr.IsDBNull(8)) sub.State = rdr.GetSqlByte(8);
                    }
                    catch (InvalidCastException ex) 
                    {
                        System.Console.Write(ex.ToString());
                    }
                   

                    subjects.Add(sub);

                }
            }

            return subjects;
        }

        /// <summary>
        /// 更新专题信息
        /// </summary>		
        /// <returns>若成功返回 true</returns>
        public Boolean UpdateSubjects(SubjectInfo subject) 
        {
            //..e
            return false;
        }

        /// <summary>
        /// 添加专题
        /// </summary>		
        /// <returns>若成功返回 true</returns>
        public int AddSubjects(SubjectInfo subject)
        {
            //..e
            return 0;
        }
        #endregion
    }
}
